<%@ Page language="vb" CodeFile="searchDealer.aspx.vb" Inherits="searchDealer_aspx_vb" %>

<%
    rowNumber = 0
    If Request.QueryString("do") = Nothing Then
        subDo = Request.QueryString("subDo")
        dealerID = Request.QueryString("dealerID")
        If Len(dealerID) > 0 Then
            dealerID = CLng(dealerID)
            dbOpen()
            RecSet = Conn.Execute("Select c.countryCurrency, (Select Count(v.vehicleID) From tblVehicle v Where v.dealerID = d.dealerID) As vehicleCount, (Select Count(c.claimID) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.dealerID = d.dealerID) As claimCount, (Select Sum(c.paymentAmount) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.dealerID = d.dealerID) As claimSum From (tblDealer d INNER JOIN tblCountry c ON c.countryID = d.countryID) Where d.dealerID = " & dealerID & "")
            If Not RecSet.EOF Then
                countryCurrency = RecSet.Fields("countryCurrency").Value
                vehicleCount = RecSet.Fields("vehicleCount").Value
                claimCount = RecSet.Fields("claimCount").Value
                claimSum = RecSet.Fields("claimSum").Value
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(claimSum) Then
                claimSum = 0
            End If
        End If
        Over()
        dbOpen()
        RecSet = Conn.Execute("Select partnerID, partnerName, countryID From tblPartner Order by partnerName")
        If Not RecSet.EOF Then
            Do Until RecSet.EOF
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & partnerID & "," & countryID & ",""" & partnerName & """),"
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        selectSub()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH CAR DEALER<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchDealer.aspx?subDo=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.partnerID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Partner:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='partnerID' id='partnerID' disabled>")
        Response.Write("<option value=''> - Select partner - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Car dealer:</td><td align='right'><input type='text' name='dealer' id='dealer' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>DealerID:</td><td align='right'><input type='text' name='dealerID' id='dealerID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Areacode:</td><td align='right'><input type='text' name='areacode' id='areacode' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Telephone:</td><td align='right'><input type='text' name='telephone' id='telephone' size='40'  maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Email:</td><td align='right'><input type='text' name='email' id='email' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        If Len(dealerID) > 0 Then
            Response.Write("<tr><td class='headline' colspan='2'>DEALER STATISTICS<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of lease contracts:</td><td align='right'>" & vehicleCount & "</td></tr>")
            Response.Write("<tr><td>Number of claims reported:</td><td align='right'>" & claimCount & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total claim cost (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimSum), 2) & "</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End If
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr><td class='headline' colspan='10'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        partnerID = Request.Form("partnerID")
        dealer = Request.Form("dealer")
        dealerID = Request.Form("dealerID")
        areacode = Request.Form("areaCode")
        telephone = Request.Form("telephone")
        email = Request.Form("email")
        If Len(dealerID) = 0 Then
            dealerID = Request.QueryString("dealerID")
        End If
        If Len(countryID) > 0 Then
            sqlCountryID = " And d.countryID = " & countryID & ""
        End If
        If Len(partnerID) > 0 Then
            sqlPartnerID = " And d.partnerID = " & partnerID & ""
        End If
        If Len(dealer) > 0 Then
            dealer = Trim(dealer)
            dealer = Replace(CStr(dealer), "'", "")
            sqlDealer = " And d.dealer Like '%" & dealer & "%'"
        End If
        If Len(dealerID) > 0 Then
            sqlDealerID = " And d.dealerID = " & dealerID & ""
        End If
        If Len(areacode) > 0 Then
            areacode = Trim(areacode)
            areacode = Replace(areacode, "'", "")
            sqlAreaCode = " And d.areaCode Like '%" & areacode & "%'"
        End If
        If Len(telephone) > 0 Then
            telephone = Trim(telephone)
            telephone = Replace(telephone, "'", "")
            sqlTelephone = " And d.telephone Like '%" & telephone & "%'"
        End If
        If Len(email) > 0 Then
            email = Trim(email)
            email = Replace(email, "'", "")
            sqlEmail = " And d.email Like '%" & email & "%'"
        End If
        If Len(subDo) = 0 Then
            sqlCountryID = " And d.countryID = 1000"
        End If
        dbOpen()
        RecSet = Conn.Execute("Select d.dealerID, d.dealer, d.city, d.partnerID, d.pwd, p.partnerName, c.country From ((tblDealer d INNER JOIN tblPartner p ON p.partnerID = d.partnerID) INNER JOIN tblCountry c ON c.countryID = d.countryID) Where d.partnerID > 0" & sqlPartnerID & sqlPartnerName & sqlCountryID & sqlDealerID & sqlDealer & sqlAreaCode & sqlTelephone & sqlEmail & " Order By d.dealer")
        If RecSet.EOF Then
            Response.Write("<tr><td><font color='red'>No customers found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Dealer</td>")
            Response.Write("<td class='formBold'>City</td>")
            Response.Write("<td class='formBold'>Username</td>")
            Response.Write("<td class='formBold'>Password</td>")
            Response.Write("<td class='formBold'>Partner</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                dealerID = RecSet.Fields("dealerID").Value
                dealer = RecSet.Fields("dealer").Value
                city = RecSet.Fields("city").Value
                partnerID = RecSet.Fields("partnerID").Value
                pwd = RecSet.Fields("pwd").Value
                partnerName = RecSet.Fields("partnerName").Value
                country = RecSet.Fields("country").Value
                If IsDBNull(pwd) = True Then
                    pwd = "N/A"
                End If
                If rowNumber = 0 Then
                    strGrey = ""
                    rowNumber = 1
                Else
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 0
                End If
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & dealer & "</td>")
                Response.Write("<td>" & city & "</td>")
                Response.Write("<td>d" & dealerID & "</td>")
                Response.Write("<td>" & pwd & "</td>")
                Response.Write("<td>" & partnerName & "</td>")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td align='right'><a href='editDealer.aspx?dealerID=" & dealerID & "'>edit dealer</a></td>")
                Response.Write("<td align='right'><a href='searchDealerEmployee.aspx?dealerID=" & dealerID & "'>view sales man</a></td>")
                Response.Write("<td align='right'><a href='searchDealer.aspx?subDo=viewVehicles&dealerID=" & dealerID & "'>view lease contracts</a></td>")
                Response.Write("<td align='right'><a href='addVehicleStep3.aspx?partnerID=" & partnerID & "&dealerID=" & dealerID & "'>place lease contract</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='10'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "viewVehicles" Then
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
            Response.Write("<tr><td class='headline' colspan='12'>LEASE CONTRACTS<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date AS tblVehicleDate, p.product, b.brand, m.model, c.country From ((((tblVehicle v INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where v.dealerID = " & dealerID & " ORDER BY v.vehicleID")
            If RecSet.EOF Then
                Response.Write("<tr><td><font color='red'>No lease contracts found.</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Car make</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Country</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 0
                Do Until RecSet.EOF
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    tblVehicleDate = RecSet.Fields("tblVehicleDate").Value
                    product = RecSet.Fields("product").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    country = RecSet.Fields("country").Value
                    If rowNumber = 0 Then
                        strGrey = ""
                        rowNumber = 1
                    Else
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 0
                    End If
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & brand & " " & model & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & country & "</td>")
                    Response.Write("<td>" & Left(Today(), 10) & "</td>")
                    Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End If
        Response.Write("</table>")
        Under()
    End If
%>
